Split the values of a single column into two or more column values, based on a separator or delimiter in the original column. This is the opposite of the Combined Columns function, which takes column values from multiple columns and combines them into a single column.
Splitting columns is useful if you have a column that contains parts that should be split across multiple columns. For instance, if you have an Address column that contains street number, street name, and city, you can split that into 3 columns. Or you may have a column listing phone numbers, from which you want to extract 2 columns: one column listing the prefixes, and a second listing the rest of the phone number.
Split a Column
The original column must contain some kind of separator between each substring that will be used to generate a new column. The new columns are extracted according to the given separator type.
Start by connecting the Split Columns node to the Select node of the table containing the column to be split. Go to the Properties panel to define the new columns:
Select Column: the column to be split.
Columns Amount: the number of columns to be generated.
Separator: the separator by which split the column. Choose the separator that exists in the column; this may be a preset in the drop-down list or a custom separator.
Keep Split Column: enable this option to keep the original column in the new table output.
Multiple Separators
Only a single separator type may be defined for each Split node. You may have a column that has multiple separators of the same type. In this case, it is not possible to specify which separator to use to split the column. By default, the split function will move through the separators sequentially, splitting the column by the first separator first, then the second, then the third, and so on, according to the given number of columns to be generated.
For instance, say your source column's values have 4 comma separators, and you want to split those values into 2 columns. In this case, the values will be split by the first comma only.
Example. Split a Column into Two
In this example, the 'Phone' column (red highlight) was split into 2 columns (blue highlight): the first column contains the prefix, and the second contains the rest of the number.
The original column contains 2 separators of the same type, in the following format: 123-456-7890. It is not possible to specify which of the 2 separators will be used to split the column. By default, the column's values are split by the separator in sequential order, from first to last. As only 2 columns are to be generated, only the first separator is used to split the column values.
Example. Split a Column into Three
If we want to split the column values at both of the separators, the 'Columns Amount' must be increased to 3, splitting the phone values into 3 columns (green highlight below):
